'''
Author: momochong0
Date: 2021-05-25 00:39:58
LastEditors: momochong0
LastEditTime: 2021-05-25 00:48:12
Description: 要推网络荣誉出品
'''
#coding=utf-8
import urllib2
import json
import jsonpath
import chardet
import time, datetime
import pymysql.cursors
import requests

# n=800
# while n<=900:
#     # 执行循环
#     url = 'http://mall.55188.com/feed/index/'+str(n)

url = 'http://mall.55188.com/feed/index/0'
session = requests.Session()
session.headers = {
    'User-Agent': 'Opera/9.80 (Windows NT 6.0) Presto/2.12.388 Version/12.14'
}
session.get('http://mall.55188.com')
r = session.get(url)
# html = r.text
# .decode('unicode-escape')

conn = pymysql.connect(host='127.0.0.1',
                       port=3306,
                       user='shares',
                       password='KKeejJeA7TL6RrtB',
                       db='shares',
                       charset="utf8mb4",
                       cursorclass=pymysql.cursors.DictCursor)
# connect()方法用于创建与数据库的连接，里面可以指定参数，这一步只是连接到了数据库，操作数据库还需要下面的游标
cur = conn.cursor()  # 通过获取到的conn数据库的cursor方法创建游标

# 把json格式字符串转换成python对象
jsonobj = json.loads(r.text)

# 从根节点开始，匹配jsonobj节点
i = 9
while i >= 0:

    titledata = jsonpath.jsonpath(jsonobj, '$..title')
    title = titledata[i][-6:]
    # print title

    contentdata = jsonpath.jsonpath(jsonobj, '$..content')
    content = contentdata[i]
    # print content

    showtimedata = jsonpath.jsonpath(jsonobj, '$..showtime')
    showtime = showtimedata[i]
    # print showtime

    wherefromdata = jsonpath.jsonpath(jsonobj, '$..wherefrom')
    wherefrom = wherefromdata[i]
    # print wherefrom

    iddata = jsonpath.jsonpath(jsonobj, '$..id')
    lxid = iddata[i]
    # print id

    isbuydata = jsonpath.jsonpath(jsonobj, '$..isbuy')
    isbuy = isbuydata[i]
    # print isbuy

    weburldata = jsonpath.jsonpath(jsonobj, '$..weburl')
    weburl = weburldata[i]
    # print weburl

    sqlstr = "insert into recommend (title,content,showtime,wherefrom,lxid,isbuy,weburl) values('%s','%s','%s','%s','%s','%s','%s')" % (
        title, content, showtime, wherefrom, lxid, isbuy, weburl)
    # print sqlstr

    qstr = "select id from recommend where lxid='%s'" % (lxid)
    # print qstr
    # print "\r\n"
    cur.execute(qstr)
    data = cur.fetchone()

    if data == None:
        print "查询结果为空，进行数据插入'\r\n'"
        cur.execute(sqlstr)
        conn.commit()  #提交数据进数据库KKeejJeA7TL6RrtB

    i = i - 1

# n=n+10

cur.close()
conn.close()  #关闭数据库连接
